package com.zmn.oms.business.impl.report;

import org.apache.dubbo.config.annotation.Reference;
import com.google.common.collect.Lists;
import com.zmn.common.dto2.ResponseDTO;
import com.zmn.common.utils.office.ExcelUtil;
import com.zmn.cube.dubbo.interfaces.dataset.DatasetRemoteService;
import com.zmn.oms.business.interfaces.report.ProviderPaymentBService;
import com.zmn.oms.model.entity.report.ReportProviderPaymentQuery;
import com.zmn.oms.model.vo.report.ReportProviderPaymentVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 服务商/厂商收付款对账表
 *
 * @author: HuangYongJie
 * @version: v1.0
 * @since: 2019/11/7 16:39
 **/

@Service
@Slf4j
public class ProviderPaymentBServiceImpl implements ProviderPaymentBService {

    /**
     * 拼接sql常量
     */
    private static final String WHERE_AND = " and ";
    private static final String WHERE_OR = " OR ";
    private static final String WHERE_BETWEEN = " between ";
    private static final String WHERE_ORDER_ID = " and t.order_id = ";
    private static final String WHERE_FC_COMPANY_ID = " and t.fc_company_id = ";
    private static final String WHERE_SP_COMPANY_ID = " and t.sp_company_id = ";
    private static final String WHERE_CUSTOMER_ID = " and t.customer_id = ";
    private static final String WHERE_ORDER_KEEP = " and t.order_keep = ";
    private static final String WHERE_PLAT = " and t.plat= ";
    private static final String WHERE_CUSTOMER_BILL_STATUS = "  t.customer_bill_status in ";
    private static final String WHERE_SP_COMPANY_TYPE = " and t.sp_company_type= ";

    private static final String WHERE_CUSTOMER_BILL_STATUS_OR_NULL = " t.customer_bill_status is null ";

    private static final String WHERE_PART_BILL_STATUS = "  t.manage_bill_status in ";
    private static final String WHERE_PART_BILL_STATUS_OR_NULL = "  t.manage_bill_status is null ";
    private static final String WHERE_MANAGE_STATEMENT_TIME = " and t.manage_statement_time ";
    private static final String WHERE_CUSTOMER_BILL_TIME = " and t.customer_statement_time ";
    private static final String WHERE_ACCOUNT_TIME = " and t.account_time ";
    private static final String WHERE_CUSTOMER_SETTLE_TYPE = " and t.fc_company_id > 0  ";
    private static final String LIMIT_STR = " limit ";
    private static final String OFFSET_STR = " offset ";
    private static final String WHERE_STR = " where t.order_after in (1,2)";
    private static final String ORDERBY_STR = " order by order_id ";

    /**
     * 厂商类型
     */
    private static final String WHERE_SETTLEMENT_TYPE = " z.settlement_type in ";
    private static final String WHERE_SETTLEMENT_TYPE_OR_NULL = "  z.settlement_type is null ";

    private static final String columnStr = "SELECT t.order_id, t.order_amount, t.fc_company_name, t.receipt_amount, t.pay_status, t.fc_company_id,\n" +
            "t.customer_statement_id, t.customer_bill_status, t.sp_company_name, t.manage_statement_id, z.settlement_type, \n" +
            "t.manage_bill_status, t.manage_statement_cost, t.customer_statement_cost,t.account_time,t.user_name,t.customer_name\n" +
            "FROM dws.dws_f_order t LEFT JOIN stg.stg_plat_fc_factory z on t.fc_company_id = z.fc_id \n";
    private static final String countColumnStr = "SELECT sum(IFNULL(order_amount,0)) order_amount, sum(IFNULL(receipt_amount,0)) receipt_amount,\n" +
            "sum(IFNULL(customer_statement_cost,0)) customer_statement_cost, sum(IFNULL(manage_statement_cost,0)) manage_statement_cost \n" +
            "FROM dws.dws_f_order t \n";

    private static final String LEFT_JOIN_TABLE = " LEFT JOIN stg.stg_plat_fc_factory z on t.fc_company_id = z.fc_id ";

    private static final String countSql = "SELECT count(*) total from dws.dws_f_order t \n";


    private static final String DATE_STR_COLUMNS = "STR_TO_DATE(#DATE_STR_COLUMNS, '%Y-%m-%d %H:%i:%s')";

    private static final String DATE_STR_REPLACE = "#DATE_STR_COLUMNS";

    private static final String BRACKETS_LEFT = "(";
    private static final String BRACKETS_RIGHT = ")";
    private static final String COMMA_STR = ",";

    private static final Integer NULL_VALUE = 0;

    /**
     * 时间格式化
     */
    private static final String DATESTRFORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final int EXPORT_MAX_ROW = 5000;
    private static final int PAGE_INDEX = 1;

    /**
     * excel的表头
     */
    private static final List PROVIDER_TITLE = Lists.newArrayList(
            "工单号",
            "订单金额",
            "实收金额",
            "客户",
            "厂商",
            "订单收付状态",
            "厂商结算单号",
            "厂商收款",
            "厂商类型",
            "厂商/收款状态",
            "服务商",
            "服务商结算单号",
            "服务商提留",
            "服务商/付款状态",
            "算账时间"
    );

    /**
     * excel的表头
     */
    private static final List MANUFACTURER_TITLE = Lists.newArrayList(
            "工单号",
            "订单金额",
            "实收金额",
            "厂商",
            "订单收付状态",
            "厂商结算单号",
            "厂商类型",
            "厂商/收款状态",
            "服务商",
            "服务商结算单号",
            "服务商提留",
            "服务商/付款状态",
            "算账时间"
    );
    private static Map<Integer, String> PAY_STATUS_MAP = new HashMap<>();
    private static Map<Integer, String> PART_BILL_STATUS_MAP = new HashMap<>();
    private static Map<Integer, String> SETTLEMENT_TYPE_MAP = new HashMap<>();

    static {
        PAY_STATUS_MAP.put(1, "未支付");
        PAY_STATUS_MAP.put(2, "支付中");
        PAY_STATUS_MAP.put(3, "部分支付");
        PAY_STATUS_MAP.put(4, "已支付");
        PAY_STATUS_MAP.put(12, "退款中");
        PAY_STATUS_MAP.put(13, "部分退款");
        PAY_STATUS_MAP.put(14, "已退款");
        PART_BILL_STATUS_MAP.put(10, "待审核");
        PART_BILL_STATUS_MAP.put(20, "待付款");
        PART_BILL_STATUS_MAP.put(30, "付款中");
        PART_BILL_STATUS_MAP.put(40, "已付款");
        PART_BILL_STATUS_MAP.put(41, "付款失败");
        PART_BILL_STATUS_MAP.put(100, "待审核");
        PART_BILL_STATUS_MAP.put(200, "审核通过");
        PART_BILL_STATUS_MAP.put(210, "审核未过");
        SETTLEMENT_TYPE_MAP.put(1, "预充值");
        SETTLEMENT_TYPE_MAP.put(2, "后结算");
    }

    @Reference(version = com.zmn.base.plat.engine.common.constant.DubboConsts.INTERFACE_VERSION , check = false)
    DatasetRemoteService datasetRemoteService;

    /**
     * 分页查询
     *
     * @param query
     * @return
     */
    @Override
    public List<ReportProviderPaymentVO> listPageProviderPayment(ReportProviderPaymentQuery query) {

        Integer count = getCountProviderPayment(query);
        /*if (NumberUtil.isNullOrZero(count)) {
            return Collections.emptyList();
        }*/
        query.setTotalCount(count);
        List<ReportProviderPaymentVO> dataList = listTableData(query);
        if (!Objects.isNull(dataList) && !dataList.isEmpty()) {
            ReportProviderPaymentVO totalRowData = getTotalRowProviderPayment(query);
            dataList.add(0, totalRowData);
        }
        return dataList;
    }

    /**
     * 获取列表数据
     *
     * @param query
     * @return
     */
    private List<ReportProviderPaymentVO> listTableData(ReportProviderPaymentQuery query) {
        String whereStr = buildSqlByQuery(query, true);
        String sql;
        if (StringUtils.isNotBlank(whereStr)) {
            sql = columnStr + WHERE_STR + whereStr;
        } else {
            sql = columnStr + WHERE_STR;
        }
        List<ReportProviderPaymentVO> dataList = doRunSqlFromDubbo(sql);
        return dataList;
    }


    private Integer getCountProviderPayment(ReportProviderPaymentQuery query) {
        String whereStr = buildSqlByQuery(query, false);
        String sql;
        if (StringUtils.isNotBlank(whereStr)) {
            if (Objects.isNull(query.getSettlementType()) || query.getSettlementType().isEmpty()) {
                sql = countSql + WHERE_STR + whereStr;
            } else {
                sql = countSql + LEFT_JOIN_TABLE + WHERE_STR + whereStr;
            }
        } else {
            sql = countSql + WHERE_STR;
        }
        log.info("getCountProviderPayment sql:" + sql);
        ResponseDTO<Integer> responseDTO = datasetRemoteService.count(sql);
        if (responseDTO == null) {
            return 0;
        }
        return responseDTO.getData();
    }

    /**
     * 获取汇总行
     *
     * @param query
     * @return
     */
    @Override
    public ReportProviderPaymentVO getTotalRowProviderPayment(ReportProviderPaymentQuery query) {
        String whereStr = buildSqlByQuery(query, false);
        String sql;
        if (StringUtils.isNotBlank(whereStr)) {
            if (Objects.isNull(query.getSettlementType()) || query.getSettlementType().isEmpty()) {
                sql = countColumnStr + WHERE_STR + whereStr;
            } else {
                sql = countColumnStr + LEFT_JOIN_TABLE + WHERE_STR + whereStr;
            }
        } else {
            sql = countColumnStr;
        }
        List<ReportProviderPaymentVO> dataList = doRunSqlFromDubbo(sql);
        ReportProviderPaymentVO vo = null;
        if (!Objects.isNull(dataList) && !dataList.isEmpty()) {
            vo = dataList.get(0);
            vo.setFcCompanyId(-100);
        }
        return vo;
    }

    /**
     * 根据sql执行查询
     *
     * @param sql
     * @return
     */
    private List<ReportProviderPaymentVO> doRunSqlFromDubbo(String sql) {
        log.info("doRunSqlFromDubbo sql:{}", sql);
        ResponseDTO<List> responseDTO = datasetRemoteService.list(sql);
        log.info("doRunSqlFromDubbo result :{}", responseDTO.getStatus());
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) responseDTO.getData();
        List<ReportProviderPaymentVO> resultList = buildVOList(dataList);
        return resultList;
    }

    /**
     * 根据条件获取查询sql
     *
     * @param query
     * @return
     */
    private String buildSqlByQuery(ReportProviderPaymentQuery query, boolean isLimit) {
        StringBuilder whereBuffer = new StringBuilder();
        if (!Objects.isNull(query.getOrderId())) {
            whereBuffer.append(WHERE_ORDER_ID).append(query.getOrderId());
        }
        if (!Objects.isNull(query.getFcCompanyId())) {
            whereBuffer.append(WHERE_FC_COMPANY_ID).append(query.getFcCompanyId());
        }
        if (!Objects.isNull(query.getSpCompanyId())) {
            whereBuffer.append(WHERE_SP_COMPANY_ID).append(query.getSpCompanyId());
        }
        if (!Objects.isNull(query.getCustomerId())) {
            whereBuffer.append(WHERE_CUSTOMER_ID).append(query.getCustomerId());
        }
        if (!Objects.isNull(query.getOrderKeep())) {
            whereBuffer.append(WHERE_ORDER_KEEP).append(query.getOrderKeep());
        }
        if (!Objects.isNull(query.getPlat())) {
            whereBuffer.append(WHERE_PLAT).append(query.getPlat());
        }
        if (!Objects.isNull(query.getSpCompanyType())) {
            whereBuffer.append(WHERE_SP_COMPANY_TYPE).append(query.getSpCompanyType());
        }

        if (!Objects.isNull(query.getCustomerSettleStatus()) && !query.getCustomerSettleStatus().isEmpty()) {
            if (Objects.isNull(query.getCustomerSettleType())) {
                whereBuffer.append(WHERE_CUSTOMER_SETTLE_TYPE);
            }
            List<Integer> statuList = query.getCustomerSettleStatus();
            StringBuilder whereStatus = formatWhereByList(statuList, WHERE_CUSTOMER_BILL_STATUS_OR_NULL, WHERE_CUSTOMER_BILL_STATUS);
            whereBuffer.append(whereStatus);

        }
        if (!Objects.isNull(query.getPartBillStatus()) && !query.getPartBillStatus().isEmpty()) {
            List<Integer> statuList = query.getPartBillStatus();
            StringBuilder whereStatus = formatWhereByList(statuList, WHERE_PART_BILL_STATUS_OR_NULL, WHERE_PART_BILL_STATUS);
            whereBuffer.append(whereStatus);
        }

        if (!Objects.isNull(query.getSettlementType()) && !query.getSettlementType().isEmpty()) {
            List<Integer> statuList = query.getSettlementType();
            StringBuilder whereStatus = formatWhereByList(statuList, WHERE_SETTLEMENT_TYPE_OR_NULL, WHERE_SETTLEMENT_TYPE);
            whereBuffer.append(whereStatus);
        }
        if (!StringUtils.isEmpty(query.getManageStatementTimeStart()) && !StringUtils.isEmpty(query.getManageStatementTimeEnd())) {
            //whereBuffer.append("and mon >= ").append(getMonWhere(query.getManageStatementTimeStart())).append(" and mon <= ").append(getMonWhere(query.getManageStatementTimeEnd()));
            whereBuffer.append(WHERE_MANAGE_STATEMENT_TIME).append(WHERE_BETWEEN).append(strToTimeStamp(query.getManageStatementTimeStart()));
            whereBuffer.append(WHERE_AND).append(strToTimeStamp(query.getManageStatementTimeEnd()));
        }
        if (!StringUtils.isEmpty(query.getCustomerStatementTimeStart()) && !StringUtils.isEmpty(query.getCustomerStatementTimeEnd())) {
            //whereBuffer.append("and mon >= ").append(getMonWhere(query.getThirdStatementTimeStart())).append(" and mon <= ").append(getMonWhere(query.getThirdStatementTimeEnd()));
            whereBuffer.append(WHERE_CUSTOMER_BILL_TIME).append(WHERE_BETWEEN).append(strToTimeStamp(query.getCustomerStatementTimeStart()));
            whereBuffer.append(WHERE_AND).append(strToTimeStamp(query.getCustomerStatementTimeEnd()));
        }
        if (!StringUtils.isEmpty(query.getAccountTimeStart()) && !StringUtils.isEmpty(query.getAccountTimeEnd())) {
            //whereBuffer.append("and mon >= ").append(getMonWhere(query.getThirdStatementTimeStart())).append(" and mon <= ").append(getMonWhere(query.getThirdStatementTimeEnd()));
            whereBuffer.append(WHERE_ACCOUNT_TIME).append(WHERE_BETWEEN).append(strToTimeStamp(query.getAccountTimeStart()));
            whereBuffer.append(WHERE_AND).append(strToTimeStamp(query.getAccountTimeEnd()));
        }

        if (!Objects.isNull(query.getCustomerSettleType())) {
            whereBuffer.append(WHERE_CUSTOMER_SETTLE_TYPE);
        }
        if (isLimit) {
            //分页
            if (!Objects.isNull(query.getPageSize())) {
                whereBuffer.append(ORDERBY_STR);
                whereBuffer.append(LIMIT_STR).append(query.getPageSize()).append(OFFSET_STR).append(query.getStartIndex());
            } else {
                throw new RuntimeException("分页参数不能为空");
            }
        }

        String whereStr = whereBuffer.toString();
        //whereStr = trimBothEndsChars(whereStr, " and");
        return whereStr;
    }

    private static String getMonWhere(String str) {
        String monStr = "'" + str.substring(0, 8).replace("-", "") + "'";
        return monStr;
    }


    /**
     * 字符串日期转时间戳
     *
     * @param fieldValue
     * @return
     */
    private Long strToTimeStamp(String fieldValue) {
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DATESTRFORMAT);
        LocalDateTime localDateTime = LocalDateTime.parse(fieldValue, dateTimeFormatter);
        //获取秒数
        Long timeStamp = localDateTime.toEpochSecond(ZoneOffset.of("+8"));
        //获取毫秒数
        //Long timeStamp = localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli();
        return timeStamp;
    }

    public static String trimBothEndsChars(String srcStr, String splitter) {
        String regex = "^" + splitter + "*|" + splitter + "*$";
        return srcStr.replaceAll(regex, WHERE_AND);
    }

    public static void transMap2Bean2(Map<String, Object> map, Object obj) {
        if (map == null || obj == null) {
            return;
        }
        try {
            BeanUtils.populate(obj, map);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    public List<ReportProviderPaymentVO> buildVOList(List<Map<String, Object>> dataList) {
        List<ReportProviderPaymentVO> resultList = Lists.newArrayList();
        if (dataList == null) {
            return resultList;
        }
        for (Map<String, Object> map : dataList) {
            resultList.add(buildVO(map));
        }
        return resultList;
    }

    /**
     * ReportProviderPaymentVO的创建
     *
     * @param map
     * @return
     */
    private ReportProviderPaymentVO buildVO(Map<String, Object> map) {
        ReportProviderPaymentVO vo = new ReportProviderPaymentVO();
        if (map == null) {
            return vo;
        }
        vo.setOrderId(getLongValue(map.get("order_id")));
        vo.setOrderAmount(getIntegerValue(map.get("order_amount")));
        vo.setReceiptAmount(getIntegerValue(map.get("receipt_amount")));
        vo.setPayStatus(getIntegerValue(map.get("pay_status")));
        vo.setFcCompanyId(getIntegerValue(map.get("fc_company_id")));
        vo.setCustomerStatementId(getLongValue(map.get("customer_statement_id")));
        vo.setCustomerBillStatus(getIntegerValue(map.get("customer_bill_status")));
        vo.setSpCompanyName((String) map.get("sp_company_name"));
        vo.setManageStatementId(getLongValue(map.get("manage_statement_id")));
        vo.setPartBillStatus(getIntegerValue(map.get("manage_bill_status")));
        vo.setManageSettleCost(getIntegerValue(map.get("manage_statement_cost")));
        vo.setCustomerBillCost(getIntegerValue(map.get("customer_statement_cost")));
        vo.setAccountTime(getLongValue(map.get("account_time")));
        vo.setSettlementType(getIntegerValue(map.get("settlement_type")));
        vo.setCustomerName((String) map.get("user_name"));
        vo.setFcCompanyName((String) map.get("fc_company_name"));
        return vo;
    }

    private Integer getIntegerValue(Object obj) {
        if (Objects.isNull(obj)) {
            return null;
        }
        if (obj instanceof Integer) {
            return (Integer) obj;
        }
        if (obj instanceof Long) {
            return ((Long) obj).intValue();
        }
        return 0;
    }

    private Long getLongValue(Object obj) {
        if (Objects.isNull(obj)) {
            return null;
        }
        if (obj instanceof Long) {
            return (Long) obj;
        }
        if (obj instanceof Integer) {
            return Long.valueOf((Integer) obj);
        }
        return 0L;
    }

    /**
     * 拼接in条件
     *
     * @param dataList
     * @param WhereIsNull
     * @param WhereIn
     * @return
     */
    private StringBuilder formatWhereByList(List<Integer> dataList, String WhereIsNull, String WhereIn) {
        if (dataList == null || dataList.isEmpty()) {
            return new StringBuilder("");
        }
        StringBuilder whereBuffer = new StringBuilder(WHERE_AND);
        if (dataList.contains(NULL_VALUE)) {
            if (dataList.size() == 1) {
                whereBuffer.append(WhereIsNull);
            } else {
                whereBuffer.append(BRACKETS_LEFT).append(WhereIsNull).append(WHERE_OR).append(WhereIn).append(formatWhereInByList(dataList)).append(BRACKETS_RIGHT);
            }
        } else {
            whereBuffer.append(WhereIn).append(formatWhereInByList(dataList));
        }
        return whereBuffer;
    }

    /**
     * formatWhereInByList
     *
     * @param dataList
     * @return
     */
    private StringBuilder formatWhereInByList(List<Integer> dataList) {
        StringBuilder sb = new StringBuilder(BRACKETS_LEFT);
        for (Integer value : dataList) {
            if (NULL_VALUE.equals(value)) {
                continue;
            }
            sb.append(value).append(COMMA_STR);
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(BRACKETS_RIGHT);
        return sb;
    }


    /**
     * 导出
     *
     * @param query
     * @return
     */
    @Override
    public Workbook exportExcel(ReportProviderPaymentQuery query, boolean isManufacturer) {
//        Integer count = getCountProviderPayment(query);
//        Integer pageSize = EXPORT_MAX_ROW;
//        if (count < EXPORT_MAX_ROW) {
//            pageSize = count;
//        }
//        query.setPageSize(pageSize);
//        query.setPageIndex(PAGE_INDEX);
        List<ReportProviderPaymentVO> providerPaymentVOList = listTableData(query);
        List<List> sheetData = Lists.newArrayList();
        sheetData.add(isManufacturer ? MANUFACTURER_TITLE : PROVIDER_TITLE);
        for (ReportProviderPaymentVO vo : providerPaymentVOList) {
            sheetData.add(buildRowData(vo, isManufacturer));
        }
        return getWorkbook(sheetData);
    }

    /**
     * 组装excel表格数据
     *
     * @param vo
     * @param isManufacturer
     * @return
     */
    private List buildRowData(ReportProviderPaymentVO vo, boolean isManufacturer) {
        Integer rowSize = isManufacturer ? MANUFACTURER_TITLE.size() : PROVIDER_TITLE.size();
        List row = Lists.newArrayListWithCapacity(rowSize);
        row.add(parseString(vo.getOrderId()));
        row.add(fenToYuan(vo.getOrderAmount()));
        row.add(fenToYuan(vo.getReceiptAmount()));
        if (!isManufacturer) {
            row.add(vo.getCustomerName());
        }
        row.add(vo.getFcCompanyName());
        row.add(PAY_STATUS_MAP.get(vo.getPayStatus()));
        row.add(parseString(vo.getCustomerStatementId()));
        if (!isManufacturer) {
            row.add(fenToYuan(vo.getCustomerBillCost()));
        }
        row.add(SETTLEMENT_TYPE_MAP.get(vo.getSettlementType()));
        row.add(PART_BILL_STATUS_MAP.get(vo.getCustomerBillStatus()));
        row.add(vo.getSpCompanyName());
        row.add(parseString(vo.getManageStatementId()));
        row.add(fenToYuan(vo.getManageSettleCost()));
        row.add(PART_BILL_STATUS_MAP.get(vo.getPartBillStatus()));
        row.add(timeStamp2Date(vo.getAccountTime()));
        return row;
    }

    /**
     * 获取workbook
     *
     * @param sheetData
     * @return
     */
    private Workbook getWorkbook(List<List> sheetData) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        ExcelUtil.parseList(sheetData, sheet, false);
        return workbook;
    }

    /**
     * 分转元
     *
     * @param price
     * @return
     */
    private BigDecimal fenToYuan(Integer price) {
        if (price == null) {
            return null;
        }
        return BigDecimal.valueOf(price).divide(new BigDecimal(100));
    }


    /**
     * 时间戳转日期
     *
     * @param seconds
     * @return
     */
    private String timeStamp2Date(Long seconds) {
        if (seconds == null) {
            return "";
        }
        SimpleDateFormat sdf = new SimpleDateFormat(DATESTRFORMAT);
        return sdf.format(new Date(Long.valueOf(seconds + "000")));
    }


    private String parseString(Long value) {
        if (value == null) {
            return "";
        }
        return String.valueOf(value);

    }


}
